package gov.pbc.nn.core.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bstek.bdf2.core.orm.AbstractDao;
import com.bstek.bdf2.core.orm.DataSourceRepository;
import com.bstek.bdf2.core.orm.ParseResult;
import com.bstek.bdf2.core.orm.jdbc.dialect.IDialect;
import com.bstek.dorado.data.provider.Criteria;
import com.bstek.dorado.data.provider.Junction;
import com.bstek.dorado.data.provider.Page;
import com.bstek.dorado.data.provider.filter.SingleValueFilterCriterion;

import gov.pbc.nn.core.ICallback;

/**
 * @since 2013-1-17
 * @author Jacky.gao
 */
public abstract class JdbcDao extends AbstractDao implements ApplicationContextAware {
	private ApplicationContext applicationContext;
	private DataSourceRepository dataSourceRepository;
	private Collection<IDialect> dialects;

	public void pagingQuery(Page<?> page, String sql, String dataSourceName, RowMapper<?> mapper,
			Map<String, Object> parameters) {
		this.pagingQuery(page, sql, dataSourceName, parameters, mapper);
	}

	public void pagingQuery(Page<?> page, String sql, String dataSourceName, Map<String, Object> parameters) {
		this.pagingQuery(page, sql, dataSourceName, parameters, null);
	}

	public void pagingQuery(Page<?> page, String sql, Map<String, Object> parameters) {
		this.pagingQuery(page, sql, null, parameters, null);
	}

	public void pagingQuery(Page<?> page, String sql, RowMapper<?> mapper, Map<String, Object> parameters) {
		this.pagingQuery(page, sql, null, parameters, mapper);
	}

	public void pagingQuery(Page<?> page, String sql, String dataSourceName, RowMapper<?> mapper) {
		pagingQuery(page, sql, null, dataSourceName, mapper);
	}

	public void pagingQuery(Page<?> page, String sql, RowMapper<?> mapper) {
		String currentDatasourceName = this.getDataSourceName(null);
		pagingQuery(page, sql, null, currentDatasourceName, mapper);
	}

	public void pagingQuery(Page<?> page, String sql, Object parameters[], RowMapper<?> mapper) {
		String currentDatasourceName = this.getDataSourceName(null);
		pagingQuery(page, sql, parameters, currentDatasourceName, mapper);
	}

	public void pagingQuery(Page<?> page, String sql, String dataSourceName) {
		pagingQuery(page, sql, null, dataSourceName, null);
	}

	public void pagingQuery(Page<?> page, String sql) {
		String currentDatasourceName = this.getDataSourceName(null);
		pagingQuery(page, sql, null, currentDatasourceName, null);
	}

	public void pagingQuery(Page<?> page, String sql, Object parameters[]) {
		String currentDatasourceName = this.getDataSourceName(null);
		pagingQuery(page, sql, parameters, currentDatasourceName, null);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private void pagingQuery(Page<?> page, String sql, Object parameters[], String dataSourceName,
			RowMapper<?> mapper) {
		dataSourceName = this.getDataSourceName(dataSourceName);
		JdbcTemplate jdbcTemplate = null;
		if (dataSourceName == null) {
			jdbcTemplate = this.getJdbcTemplate();
		} else {
			jdbcTemplate = this.getJdbcTemplate(dataSourceName);
		}
		IDialect dialect = getDialect(jdbcTemplate);
		if (dialect == null) {
			throw new RuntimeException("无法找到与当前数据连接匹配的数据库方言类");
		}
		String querySql = dialect.getPaginationSql(sql, page.getPageNo(), page.getPageSize());
		String countSql = "select count(*) from (" + sql + ") sub_table_alias_";
		if (parameters != null) {
			if (mapper == null) {
				page.setEntities((List) jdbcTemplate.queryForList(querySql, parameters));
			} else {
				page.setEntities((List) jdbcTemplate.query(querySql, parameters, mapper));
			}
			page.setEntityCount(jdbcTemplate.queryForObject(countSql, parameters, Integer.class));
		} else {
			if (mapper == null) {
				page.setEntities((List) jdbcTemplate.queryForList(querySql));
			} else {
				page.setEntities((List) jdbcTemplate.query(querySql, mapper));
			}
			page.setEntityCount(jdbcTemplate.queryForObject(countSql, Integer.class));
		}
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private void pagingQuery(Page<?> page, String sql, String dataSourceName, Map<String, Object> parameters,
			RowMapper<?> mapper) {
		dataSourceName = this.getDataSourceName(dataSourceName);
		NamedParameterJdbcTemplate namedjdbcTemplate = this.getNamedParameterJdbcTemplate(dataSourceName);
		String querySql = this.getDialect(this.getJdbcTemplate(dataSourceName)).getPaginationSql(sql, page.getPageNo(),
				page.getPageSize());
		String countSql = "select count(*) from (" + sql + ") sub_table_alias_";
		if (parameters != null) {
			if (mapper == null) {
				page.setEntities((List) namedjdbcTemplate.queryForList(querySql, parameters));
			} else {
				page.setEntities((List) namedjdbcTemplate.query(querySql, parameters, mapper));
			}
			page.setEntityCount(namedjdbcTemplate.queryForObject(countSql, parameters, Integer.class));
		} else {
			JdbcTemplate jdbcTemplate = this.getJdbcTemplate(dataSourceName);
			if (mapper == null) {
				page.setEntities((List) jdbcTemplate.queryForList(querySql));
			} else {
				page.setEntities((List) jdbcTemplate.query(querySql, mapper));
			}
			page.setEntityCount(jdbcTemplate.queryForObject(countSql, Integer.class));
		}
	}

	public JdbcTemplate getJdbcTemplate() {
		String currentDataSourceName = this.getDataSourceName(null);
		if (currentDataSourceName == null) {
			return this.dataSourceRepository.getDefaultJdbcTemplate();
		} else {
			Map<String, JdbcTemplate> map = this.dataSourceRepository.getJdbcTemplateMap();
			if (map.containsKey(currentDataSourceName)) {
				return map.get(currentDataSourceName);
			} else {
				return this.dataSourceRepository.getDefaultJdbcTemplate();
			}
		}
	}

	public JdbcTemplate getJdbcTemplate(String dataSourceName) {
		if (dataSourceName == null) {
			return this.dataSourceRepository.getDefaultJdbcTemplate();
		} else {
			Map<String, JdbcTemplate> map = this.dataSourceRepository.getJdbcTemplateMap();
			if (map.containsKey(dataSourceName)) {
				return map.get(dataSourceName);
			} else {
				return this.dataSourceRepository.getDefaultJdbcTemplate();
			}
		}
	}

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		String currentDataSourceName = this.getDataSourceName(null);
		return this.getNamedParameterJdbcTemplate(currentDataSourceName);
	}

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(String dataSourceName) {
		if (StringUtils.isNotEmpty(dataSourceName)) {
			return this.dataSourceRepository.getNamedParameterJdbcTemplate(dataSourceName);
		} else {
			return this.dataSourceRepository.getDefaultNamedParameterJdbcTemplate();
		}
	}

	protected IDialect getDialect(JdbcTemplate jdbcTemplate) {
		return jdbcTemplate.execute(new ConnectionCallback<IDialect>() {
			public IDialect doInConnection(Connection connection) throws SQLException, DataAccessException {
				IDialect result = null;
				for (IDialect dialect : dialects) {
					if (dialect.support(connection)) {
						result = dialect;
						break;
					}
				}
				return result;
			}
		});
	}

	protected ApplicationContext getApplicationContext() {
		return applicationContext;
	}

	public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
		this.applicationContext = applicationContext;
		Collection<DataSourceRepository> dataSourceRepositoryCollection = applicationContext
				.getBeansOfType(DataSourceRepository.class).values();
		if (dataSourceRepositoryCollection.size() > 0) {
			this.dataSourceRepository = dataSourceRepositoryCollection.iterator().next();
		} else if (applicationContext.getParent() != null) {
			dataSourceRepositoryCollection = applicationContext.getParent().getBeansOfType(DataSourceRepository.class)
					.values();
			if (dataSourceRepositoryCollection.size() > 0) {
				this.dataSourceRepository = dataSourceRepositoryCollection.iterator().next();
			}
		}
		this.dialects = applicationContext.getBeansOfType(IDialect.class).values();
	}

	@Override
	public ParseResult parseCriteria(Criteria criteria, boolean useParameterName, String alias) {
		return super.parseCriteria(criteria, useParameterName, alias);
	}

	/**
	 * 执行存储过程，并返回结果集（如果存储过程没有返回结果集，则该方法返回空的List）。<br/>
	 * 该方法支持具有出参形式的存储过程。</br>
	 * 
	 * @param procedureName
	 *            存储过程名称
	 * @param paramSize
	 *            存储过程参数个数
	 * @param inTypes
	 *            Map类型，键是入参索引，值是入参类型，即java.sql.Types中定义的类型。
	 * @param inParams
	 *            Map类型，入参参数。键是入参所依，值是入参参数值。
	 * @param outTypes
	 *            Map类型，键是出参索引，值是出参类型，即即java.sql.Types中定义的类型。
	 * @param outValues
	 *            Map类型，键是出参索引，值是出参的值。调用着只需要传入一个Map实例即可，调用后所有的出参值都从此处获取。
	 * @return List 如果存储过程有结果集，则封装为List返回。
	 */
	public List<Map<String, Object>> execProcedure(final String procedureName, int paramSize,
			final Map<Integer, Integer> inTypes, final Map<Integer, Object> inParams,
			final Map<Integer, Integer> outTypes, final Map<Integer, Object> outValues) {
		String strSql = " { call " + procedureName;
		String s = "";
		for (int i = 0; i < paramSize; i++) {
			if (i == paramSize - 1) {
				s += "?";
			} else {
				s += "?,";
			}
		}
		if (s.length() > 0) {
			s = " ( " + s + " ) ";
		}
		strSql += s + " } ";

		final String sql = strSql;
		return this.getJdbcTemplate().execute(sql, new CallableStatementCallback<List<Map<String, Object>>>() {

			@Override
			public List<Map<String, Object>> doInCallableStatement(CallableStatement call)
					throws SQLException, DataAccessException {
				if (outTypes != null) {
					for (Integer outIndex : outTypes.keySet()) {
						call.registerOutParameter(outIndex, outTypes.get(outIndex));
					}
				}
				for (Integer inIndex : inParams.keySet()) {
					call.setObject(inIndex, inParams.get(inIndex), inTypes.get(inIndex));
				}
				List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
				if (call.execute()) {
					ResultSet rs = call.getResultSet();
					if (rs != null) {
						ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
						int columnCount = md.getColumnCount();

						Map<String, Object> rowData = new HashMap<String, Object>();
						while (rs.next()) {
							rowData = new HashMap<String, Object>(columnCount);
							for (int i = 1; i <= columnCount; i++) {
								rowData.put(md.getColumnName(i), rs.getObject(i));
							}
							list.add(rowData);
						}
					}
				}
				if (outTypes != null && outValues != null) {
					for (Integer outIndex : outTypes.keySet()) {
						outValues.put(outIndex, call.getObject(outIndex));
					}
				}
				return list;
			}

		});
	}

	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	public <T> List<T> execProcedure(final String procedureName, int paramSize, final Map<Integer, Integer> inTypes,
			final Map<Integer, Object> inParams, final Map<Integer, Integer> outTypes,
			final Map<Integer, Object> outValues, final ICallback<ResultSet, T> callback) {
		String strSql = " { call " + procedureName;
		String s = "";
		for (int i = 0; i < paramSize; i++) {
			if (i == paramSize - 1) {
				s += "?";
			} else {
				s += "?,";
			}
		}
		if (s.length() > 0) {
			s = " ( " + s + " ) ";
		}
		strSql += s + " } ";

		final String sql = strSql;
		return this.getJdbcTemplate().execute(sql, new CallableStatementCallback<List<T>>() {

			@Override
			public List<T> doInCallableStatement(CallableStatement call) throws SQLException, DataAccessException {
				if (outTypes != null) {
					for (Integer outIndex : outTypes.keySet()) {
						call.registerOutParameter(outIndex, outTypes.get(outIndex));
					}
				}
				for (Integer inIndex : inParams.keySet()) {
					call.setObject(inIndex, inParams.get(inIndex), inTypes.get(inIndex));
				}
				List<T> list = new ArrayList<T>();
				if (call.execute()) {
					ResultSet rs = call.getResultSet();
					while (rs != null && rs.next()) {
						list.add(callback.execute(rs));
					}
				}
				if (outTypes != null && outValues != null) {
					for (Integer outIndex : outTypes.keySet()) {
						outValues.put(outIndex, call.getObject(outIndex));
					}
				}
				return list;
			}

		});
	}

	/**
	 * 判断过滤条件中是否存在某属性
	 * 
	 * @param propery
	 * @param criteria
	 * @return
	 */
	public boolean containsProperty(String propery, Criteria criteria) {
		if (criteria == null) {
			return false;
		}
		for (com.bstek.dorado.data.provider.Criterion c : criteria.getCriterions()) {
			if (c instanceof SingleValueFilterCriterion) {
				SingleValueFilterCriterion fc = (SingleValueFilterCriterion) c;
				if (StringUtils.equals(propery, fc.getProperty())) {
					return true;
				}
			}
			if (c instanceof Junction) {
				Junction jun = (Junction) c;
				Collection<com.bstek.dorado.data.provider.Criterion> subCriterions = jun.getCriterions();
				if (subCriterions != null) {
					return buildCriterions(propery, subCriterions);
				}
			}
		}
		return false;
	}

	private boolean buildCriterions(String propery, Collection<com.bstek.dorado.data.provider.Criterion> criterions) {
		for (com.bstek.dorado.data.provider.Criterion c : criterions) {
			if (c instanceof SingleValueFilterCriterion) {
				SingleValueFilterCriterion fc = (SingleValueFilterCriterion) c;
				if (StringUtils.equals(propery, fc.getProperty())) {
					return true;
				}
			}
			if (c instanceof Junction) {
				Junction jun = (Junction) c;
				Collection<com.bstek.dorado.data.provider.Criterion> subCriterions = jun.getCriterions();
				if (subCriterions != null) {
					return buildCriterions(propery, subCriterions);
				}
			}
		}
		return false;
	}
}
